package com.iweb.dao.impl;

import com.iweb.dao.StudentDao;
import com.iweb.entity.Student;
import com.iweb.util.DBHelper;
import com.iweb.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author WuYongheng
 * @date 2021/11/29
 * @description
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public int insert(Student student) throws SQLException {
        String sql = "insert into student values(?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd'),?,?)";
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DBHelper.getConnection();
            statement = conn.prepareStatement(sql);
            // 为占位符准备数据
            statement.setInt(1, student.getStudentNo());
            statement.setString(2, student.getLoginPwd());
            statement.setString(3, student.getStudentName());
            statement.setInt(4, student.getSex());
            statement.setInt(5, student.getGradeId());
            statement.setString(6, student.getPhone());
            statement.setString(7, student.getAddress());
            statement.setObject(8, StringUtil.dateToString(student.getBornDate()));
            statement.setString(9, student.getEmail());
            statement.setString(10, student.getIdentityCard());
            // 执行插入操作
            return statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            DBHelper.close(statement, conn);
        }
    }

    @Override
    public int update(Student student) throws SQLException {
        int s = 0;
        String sql = "update student set studentName=? where loginPwd=?";
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DBHelper.getConnection();
            statement = conn.prepareStatement(sql);
            statement.setString(1, student.getStudentName());
            statement.setString(2, student.getLoginPwd());
            int i = statement.executeUpdate();
            if (i > 0) {
                s = 1;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(statement, conn);
        }
        return s;
    }

    @Override
    public int delete(Integer studentNo) throws SQLException {
        int s = 0;
        String sql = "delete from student where studentNo=?";
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DBHelper.getConnection();
            statement = conn.prepareStatement(sql);
            statement.setInt(1, studentNo);
            int i = statement.executeUpdate();
            if (i > 0) {
                s = 1;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(statement, conn);
        }
        return s;
    }

    @Override
    public Student selectById(Integer studentNo) throws SQLException {

        String sql = "select * from student where studentNo = ? ";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = DBHelper.getConnection();
            statement = conn.prepareStatement(sql);
            statement.setInt(1, studentNo);
            System.out.println(sql);
            resultSet = statement.executeQuery();
            System.out.println(resultSet);

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            DBHelper.close(statement, conn);

        }
        return null;
    }

    @Override
    public List<Student> selectByName(String studentName) throws SQLException {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Student> studentList = new ArrayList<>();
        try {
            String sql = "select * from student where studentName like '% studentName %' ";
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);

            Student student = null;
            while (resultSet.next()) {
                student = new Student();
                student.setStudentNo(resultSet.getInt("studentNo"));
                student.setLoginPwd(resultSet.getString("loginPwd"));
                student.setStudentName(resultSet.getString("studentName"));
                student.setSex(resultSet.getInt("sex"));
                student.setGradeId(resultSet.getInt("gradeId"));
                student.setPhone(resultSet.getString("phone"));
                student.setAddress(resultSet.getString("address"));
                student.setEmail(resultSet.getString("email"));
                student.setIdentityCard(resultSet.getString("identityCard"));

                studentList.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(statement, conn);
        }
        return studentList;
    }
}
